﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Drawing;
using MySql.Data;
using MySql.Data.MySqlClient;
using ConfigLibrary;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows.Forms;

namespace TridentReport
{
    class MySqlConn
    {
        public static DataSet trident = new DataSet();
        public MySqlConnection conn = new MySqlConnection();
        private MySqlCommand cmd = new MySqlCommand();
        private MySqlDataAdapter da = new MySqlDataAdapter();
        public static DateTime dt = DateTime.Today;
        
        //config settings
        private Config config;
        private string _username;
        private string _host;
        private string _pass;
        private string _database;
        private int _port;

        public MySqlConn()
        {
            //loads the config file
            try
            {
                Stream s;
            #if (DEBUG)
                {
                    s = File.Open(TridentReport.Properties.Settings.Default.CONFIGDEV, FileMode.Open);
                }
            #elif! (DEBUG)
                {
                    s = File.Open(TridentReport.Properties.Settings.Default.CONFIGDEV, FileMode.Open);
                }
            #endif              
                BinaryFormatter b = new BinaryFormatter();
                config = (Config)b.Deserialize(s);
                s.Close();
                config.GetSqlInfo(out _host, out _username, out _pass, out _database, out _port);
            }
            catch (Exception ex)
            {
                
            }
            //end load
        }

        public void Connect(string password)
        {
            try
            {
                string[] database = _database.Split(new Char[] {','});
                password = _pass;
                conn = new MySqlConnection();
                conn.ConnectionString = "Database=" + database[1] + ";Data Source=" + _host + ";Port=" + _port + ";User Id=" + _username + ";Password=" + _pass;
                //conn.ConnectionString = "Database=contacts;Data Source = localhost; User Id = root;Password=namestradd"; 
                conn.Open();
                GC.KeepAlive(conn);
            }
            catch (Exception ex)
            {
                
            }
        }

        public void loadtrident()
        {
            string date = Convert.ToString(dt);            
            string command = "Select * from schedule.trident where rundate = '" + date + "'";
            cmd.CommandText = command;
            cmd.Connection = conn;
            da.SelectCommand = cmd;
            da.TableMappings.Add("Table", "trident");
            da.FillSchema(trident, SchemaType.Source, "trident");
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.Fill(trident, "trident");
            cmd.ExecuteNonQuery();
        }

        public void Disconnect()
        {
            conn.Close();
        }
    }
}
